#!/bin/bash

#输出路径
output_path=/db_backup/database_delete_interface
if [ ! -d $output_path ]
then
        mkdir -p $output_path
fi

#if [ $# -ne 5 ]
#then
#        echo "usage: $0 <db_ip> <db_port> <db_username> <db_password> <db_database>"
#        exit
#fi

#删除指定日期之前的接口数据，默认保留1个月（30天）（需配置）
DATE_Ymd=$(date -d "-30 day" +%Y%m%d)
#echo $DATE_Ymd

#数据库配置信息
#db_ip=$1                    # 172.16.112.21
#db_port=$2                  # 3306
#db_username=$3              # root
#db_password=$4              # airport
#db_database=$5              # db_oil_pvg

# 接口数据库（需配置,多个接口库用空格分隔）
db_list=("second-research-institute-tna")

#配置通用命令
mysql_org="mysql -h$db_ip -P$db_port -u$db_username -p$db_password -D$db_database -N"

#脚本执行结果输出日志
FILE_LOG_INFO="$output_path/$db_database-$(date '+%Y%m%d%H%M%S').log"

# 打印当前配置的需要删除的数据库列表
n=${#db_list[@]}
echo -e "需要删除过期数据的接口数据库列表：" | tee -a $FILE_LOG_INFO
for((i=0;i<n;i++))
do
	echo ${db_list[i]}
done

for((i=0;i<n;i++))
do
	db=${db_list[i]}
	echo -e "\n正在查找服务器[$db_ip]上数据库[$db]需要删除过期数据的表名称...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
	sql="SELECT a.TABLE_SCHEMA,a.TABLE_NAME,GROUP_CONCAT(a.COLUMN_NAME ORDER BY ORID) AS COLUMN_NAME
		FROM
		(
		SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,
		(CASE WHEN COLUMN_NAME='dflop' then 1 WHEN COLUMN_NAME='flop' THEN 2 WHEN COLUMN_NAME LIKE 'c%' THEN 3 WHEN COLUMN_NAME='ddtm' then 4 ELSE 5 end) AS ORID
		FROM information_schema.\`COLUMNS\` t
		WHERE t.TABLE_SCHEMA IN('$db') AND TABLE_NAME LIKE '%_HIS' AND (COLUMN_NAME LIKE '%flop' OR COLUMN_NAME LIKE '%date' OR COLUMN_NAME='ddtm')
		ORDER BY TABLE_SCHEMA,TABLE_NAME,(CASE WHEN COLUMN_NAME='dflop' then 1 WHEN COLUMN_NAME='flop' THEN 2 WHEN COLUMN_NAME LIKE 'c%' THEN 3 WHEN COLUMN_NAME='ddtm' then 4 ELSE 5 end)
		) a
		GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME;"
	#echo -e "$sql"
	table_info=$($mysql_org -e"$sql")
	#echo "$table_info"
	echo -e "\n查找服务器[$db_ip]上数据库[$db]需要删除过期数据的表名称结束...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
	echo -e "\n开始删除服务器[$db_ip]上数据库[$db]指定日期[$DATE_Ymd]之前需要删除的表记录...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
	while read TABLE_SCHEMA TABLE_NAME COLUMN_NAME
	do
        	if [[ $COLUMN_NAME != "" && "$COLUMN_NAME" != "NULL" ]]
        	then
                	echo -e "\n正在删除数据库[$TABLE_SCHEMA]表[$TABLE_NAME]指定日期[$DATE_Ymd]之前的数据...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
                sql_delete="DELETE FROM \`$TABLE_SCHEMA\`.\`$TABLE_NAME\` WHERE (CASE WHEN LENGTH(COALESCE($COLUMN_NAME))=8 THEN COALESCE($COLUMN_NAME) ELSE DATE_FORMAT(COALESCE($COLUMN_NAME),'%Y%m%d') END)<'$DATE_Ymd';"
                echo $sql_delete
                $mysql_org -e "$sql_delete"
                if [ $? -ne 0 ]
                then
                        echo "删除数据库[$TABLE_SCHEMA]表[$TABLE_NAME]指定日期[$DATE_Ymd]之前的数据失败...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
                fi
                echo -e "数据库[$TABLE_SCHEMA]表[$TABLE_NAME]指定日期[$DATE_Ymd]之前的数据已经删除...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
        fi
	done< <(echo "$table_info")
	echo -e "\n服务器[$db_ip]上数据库[$db]指定日期[$DATE_Ymd]之前需要删除的数据已经全部删除完毕...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
done

echo -e "\n服务器[$db_ip]上指定日期[$DATE_Ymd]之前需要删除的数据已经全部删除完毕...$(date '+%Y-%m-%d %H:%M:%S')" | tee -a $FILE_LOG_INFO
